<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">











<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <title>jxls - Processing JDBC ResultSet</title>
    <style type="text/css" media="all">
      @import url("../css/maven-base.css");
      @import url("../css/maven-theme.css");
      @import url("../css/site.css");
    </style>
    <link rel="stylesheet" href="../css/print.css" type="text/css" media="print" />
          <meta name="author" content="Leonid Vysochyn" />
        <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
      </head>
  <body class="composite">
    <div id="banner">
                  <a href="http://jxls.sourceforge.net" id="bannerLeft">
    
            jXLS
    
            </a>
                          <span id="bannerRight">
    
                                    <img src="http://sourceforge.net/sflogo.php?group_id=141729&type=1" alt="" />
    
            </span>
            <div class="clear">
        <hr/>
      </div>
    </div>
    <div id="breadcrumbs">
          
  

  
    
  
  
    
            <div class="xleft">
        Last Published: 13 Nov 2014
                      </div>
            <div class="xright">            <a href="http://sourceforge.net/projects/jxls" class="externalLink">jXLS on Sourceforge</a>
            
  

  
    
  
  
    
  </div>
      <div class="clear">
        <hr/>
      </div>
    </div>
    <div id="leftColumn">
      <div id="navcolumn">
           
  

  
    
  
  
    
                   <h5>Overview</h5>
            <ul>
              
    <li class="none">
                    <a href="../index.html">Home</a>
          </li>
              
    <li class="none">
                    <a href="http://sourceforge.net/project/showfiles.php?group_id=141729" class="externalLink">Downloads</a>
          </li>
              
    <li class="none">
                    <a href="../reference/installation.html">Installation</a>
          </li>
          </ul>
              <h5>Reference</h5>
            <ul>
              
    <li class="none">
                    <a href="../reference/simplebeans.html">Simple Beans</a>
          </li>
              
    <li class="none">
                    <a href="../reference/tags.html">Using Tags</a>
          </li>
              
    <li class="none">
                    <a href="../reference/sqlreporting.html">SQL Reporting</a>
          </li>
              
    <li class="none">
                    <a href="../reference/collections.html">Collections</a>
          </li>
              
    <li class="none">
                    <a href="../reference/advancedcollections.html">Advanced Collections</a>
          </li>
              
    <li class="none">
                    <a href="../reference/dynamicsheets.html">Dynamic Sheets</a>
          </li>
              
    <li class="none">
                    <a href="../reference/expressions.html">Using Expressions</a>
          </li>
              
    <li class="none">
                    <a href="../reference/formulas.html">Formulas Notation</a>
          </li>
              
    <li class="none">
                    <a href="../reference/poiaccess.html">POI Objects Access</a>
          </li>
              
    <li class="none">
                    <a href="../reference/columnhiding.html">Column Hiding</a>
          </li>
              
    <li class="none">
                    <a href="../reference/sheetoperations.html">Spreadsheet Operations</a>
          </li>
              
    <li class="none">
                    <a href="../reference/customprocessor.html">Custom Processor</a>
          </li>
              
    <li class="none">
                    <a href="../reference/resultset.html">JDBC ResultSet</a>
          </li>
              
    <li class="none">
                    <a href="../reference/reader.html">XLS Reader</a>
          </li>
          </ul>
              <h5>Samples</h5>
            <ul>
              
    <li class="none">
                    <a href="../samples/tagsample.html">Tags Sample</a>
          </li>
              
    <li class="none">
                    <a href="../samples/chartsample.html">Charts</a>
          </li>
              
    <li class="none">
                    <a href="../samples/reportsample.html">Reporting Sample</a>
          </li>
              
    <li class="none">
                    <a href="../samples/collectionsample.html">Collections</a>
          </li>
              
    <li class="none">
                    <a href="../samples/adjacentlists.html">Adjacent Lists</a>
          </li>
              
    <li class="none">
                    <a href="../samples/dynamicstylesample.html">Dynamic Style</a>
          </li>
              
    <li class="none">
              <strong>JDBC ResultSet</strong>
        </li>
              
    <li class="none">
                    <a href="../samples/dynamiccolumnssample.html">Dynamic Columns</a>
          </li>
          </ul>
              <h5>Project Documentation</h5>
            <ul>
              
                
              
      
            
      
            
      
              
        <li class="collapsed">
                    <a href="../project-info.html">Project Information</a>
                </li>
          </ul>
                                           <a href="http://maven.apache.org/" title="Built by Maven" class="poweredBy">
            <img alt="Built by Maven" src="../images/logos/maven-feather.png"></img>
          </a>
                       
  

  
    
  
  
    
        </div>
    </div>
    <div id="bodyColumn">
      <div id="contentBox">
        <div class="section"><h2><a name="Overview"></a>Overview</h2>
<p>Basics of processing JDBC ResultSet with <a class="externalLink" href="http://jxls.sourceforge.net">jXLS</a> is described
                <a href="reference/resultset.html">here</a>.
            </p>
<p> Next samples assume we have a table
                <code>employee</code>  with columns
                <code>name, age, payment, bonus, birthDate</code>. XLS template is the same as in
                <a href="collectionsample.html">Simple collection sample</a></p>
<img src="../images/employeesTemplateXLS.gif" alt="Screenshot of Employees XLS template file" /><p>XLS file
                <a href="../xls/employees.xls">employees.xls</a></p>
<div class="section"><h3><a name="ResultSet"></a>ResultSet</h3>
<p>This is a source code snippet to query data using
                    <code>org.apache.commons.beanutils.ResultSetDynaClass</code> from <a class="externalLink" href="http://commons.apache.org/beanutils/">Commons BeanUtils</a> library.
				</p>
<div class="source"><pre>
		                Connection con = ...// get connection
				        Statement stmt = con.createStatement();
				        String query = &quot;SELECT name, age, payment, bonus, birthDate FROM employee&quot;;
				        ResultSet rs = stmt.executeQuery(query);
				        Map beans = new HashMap();
				        ResultSetCollection rsc = new ResultSetCollection(rs, false);
				        beans.put( &quot;employee&quot;, rsc );
				        XLSTransformer transformer = new XLSTransformer();
				        transformer.transformXLS( templateFileName, beans, destFileName);
                </pre>
</div>
<p>An important problem with this approach is that the underlying ResultSet must remain open throughout the period of time that the rows are being processed by jXLS engine. Next approach does not have such restriction. </p>
</div>
<div class="section"><h3><a name="RowSet"></a>RowSet</h3>
<p> When you construct an instance of <code>org.apache.commons.beanutils.RowSetDynaClass</code> from <a class="externalLink" href="http://commons.apache.org/beanutils/">Commons BeanUtils</a>,
                    the underlying data are copied into a set of in-memory DynaBeans that represent the result.
                    So you can immediately close the <code>ResultSet</code>, normally before you even process the actual data that was returned.
                    But the disadvantage is that you must pay the performance and memory costs of copying the result data.
                    Next code snippet demonstrates how to use RowSet to export JDBC query results. </p>
<div class="source"><pre>
	                    Connection con = ...// get connection
				        Statement stmt = con.createStatement();
				        String query = &quot;SELECT name, age, payment, bonus, birthDate FROM employee&quot;;
				        ResultSet rs = stmt.executeQuery(query);
				        // second parameter to RowSetDynaClass constructor indicates
                        // that properties should not be lowercased
				        RowSetDynaClass rsdc = new RowSetDynaClass(rs, false);
				        Map beans = new HashMap();
				        beans.put( &quot;employee&quot;, rsdc.getRows() );
				        XLSTransformer transformer = new XLSTransformer();
				        transformer.transformXLS( templateFileName, beans, destFileName);
                </pre>
</div>
<p>Result looks like</p>
<img src="../images/employeesOutputXLS.gif" alt="Screenshot of Employees XLS output" /><p> Excel file is
                    <a href="../xls/employees_output.xls">employees_output.xls</a></p>
</div>
</div>

      </div>
    </div>
    <div class="clear">
      <hr/>
    </div>
    <div id="footer">
      <div class="xright">&#169;  
          2005-2014
    
          jXLS Team
          
  

  
    
  
  
    
  </div>
      <div class="clear">
        <hr/>
      </div>
    </div>
  </body>
</html>
